﻿using Infrastructure.Dao;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WsdShop.IService;
using WsdShop.Model;
using WsdShop.Model.SearchModels;
using WsdShop.Model.ViewModels;

namespace WsdShop.Service
{
    public class ReportService : IReportService
    {
        private SqlSugarClient db;
        /// <summary>
        /// 发货单报表
        /// </summary>
        /// <param name="orderno"></param>
        /// <param name="begintime"></param>
        /// <param name="endtime"></param>
        /// <param name="ordersendstatus"></param>
        /// <returns></returns>
        public DataTable GetDeliverGoods(string orderno, string begintime, string endtime, int ordersendstatus, int ordertype)
        {
            using (db = DbConfig.GetInstance())
            {
                string sql =
                    $@"select a.OrderNo,b.InvoiceNo,b.AddTime,o.MemberId,o.MemberName,a.ProductName,a.SpecName,a.DiscountPrice as Price ,ProductCount,a.DiscountPrice*a.ProductCount as TotalPrice, o.ReceiveName,o.ReceivePhone,c.Name+d.Name as ReceivePC,o.ReceiveAddress,o.PayTime,o.AddTime as OrderTime from OrderDetail a inner join Delivery b on a.DeliveryId=b.Id inner join [Order] o on a.OrderId=o.Id inner join Common_Area c on o.ReceiveProvince=c.Id inner join Common_Area d on o.ReceiveCity=d.Id  where a.IsDeleted=0 and o.IsDeleted=0 ";
                if (!string.IsNullOrEmpty(begintime) && !string.IsNullOrEmpty(endtime) && begintime != "undefined" && endtime != "undefined")
                {
                    sql += " and b.AddTime>'" + begintime + "' and b.AddTime<'" + endtime + "'";
                }
                if (!string.IsNullOrEmpty(orderno) && orderno != "undefined")
                {
                    sql += " and a.OrderNo='" + orderno + "'";
                }
                if (ordertype >= 0)
                {
                    sql += " and o.OrderType='" + ordertype + "'";
                }
                var dt = db.GetDataTable(sql);
                return dt;
            }
        }
        /// <summary>
        /// 订单报表
        /// </summary>
        /// <param name="orderno"></param>
        /// <param name="begintime"></param>
        /// <param name="endtime"></param>
        /// <param name="orderstatus"></param>
        /// <param name="paystatus"></param>
        /// <param name="sendstatus"></param>
        /// <returns></returns>
        public DataTable GetOrder(string orderno, string begintime, string endtime, int orderstatus, int paystatus, int sendstatus,int ordertype)
        {
            using (db = DbConfig.GetInstance())
            {
                string sql =
                    $@"select o.OrderNo,case OrderState when 0 then '待确认' when 1 then '已确认' when 2 then '已收货' when 3 then '已完成' when 4 then '已取消' when 5 then '已作废' else '未知'  end as OrderState,ProductName,SpecName,a.DiscountPrice as Price ,ProductCount,a.DiscountPrice*a.ProductCount as TotalPrice,o.ProductPrice,PostageMoney,PointsPayMoney,ActivityCouponMoney,MemberMoney,PayMoney,UsePoints,case OrderType when 0 then '正常订单' when 1 then '积分订单' when 2 then '团购订单' when 3 then '抢购订单' when 4 then '一分钱订单' else '未知'  end as OrderType,case PayState when 0 then '未支付' when 1 then '已支付' else '未知'  end as PayState,PayTime,case a.SendStatus when 0 then '未发货' when 1 then '已发货' when 2 then '已换货' when 3 then '已退货' when 4 then '确认收货' else '未知'  end as SendStatus,o.AddTime from OrderDetail a inner join  [Order] o on a.OrderId=o.Id  where a.IsDeleted=0 and o.IsDeleted=0 and OrderType!=4";
                if (!string.IsNullOrEmpty(begintime) && !string.IsNullOrEmpty(endtime) && begintime != "undefined" && endtime != "undefined")
                {
                    sql += " and o.AddTime>'" + begintime + "' and o.AddTime<'" + endtime + "'";
                }
                if (!string.IsNullOrEmpty(orderno) && orderno != "undefined")
                {
                    sql += " and o.OrderNo='" + orderno + "'";
                }
                if (orderstatus > 0)
                {
                    sql += " and OrderState=" + orderstatus + "";
                }
                if (paystatus > 0)
                {
                    sql += " and PayState=" + paystatus + "";
                }
                if (sendstatus > 0)
                {
                    sql += " and o.SendStatus=" + sendstatus + "";
                }
                if (ordertype > 0)
                {
                    sql += " and OrderType=" + ordertype + "";
                }
                sql += " order by o.Id desc";
                var dt = db.GetDataTable(sql);
                return dt;
            }
        }
        /// <summary>
        /// 退货单报表
        /// </summary>
        /// <param name="orderno"></param>
        /// <param name="begintime"></param>
        /// <param name="endtime"></param>
        /// <param name="satus"></param>
        /// <param name="returntype"></param>
        /// <returns></returns>
        public DataTable GetRerurnGoods(string orderno, string begintime, string endtime, int satus, int returntype)
        {
            using (db = DbConfig.GetInstance())
            {
                string sql =
                    $@"select r.ReturnNo, r.OrderNo,case r.ReturnType when 0 then '退货' when 1 then '换货' else '未知'  end as ReturnType,r.AddTime,case r.Satus when 0 then '待处理' when 1 then '客服处理中' when 2 then '已拒绝' when 3 then '全部退款' when 4 then '已换货' when 5 then '部分退款' else '未知'  end as Satus,a.ProductName,SpecName,a.DiscountPrice as Price ,ProductCount,a.DiscountPrice*a.ProductCount as TotalPrice,ReturnAmount ,PostageMoney,PointsPayMoney,ActivityCouponMoney,case OrderType when 0 then '正常订单' when 1 then '积分订单' when 2 then '团购订单' when 3 then '抢购订单' when 4 then '一分钱订单' else '未知'  end as OrderType,r.Reason from ReturnGoods r inner join OrderDetail a on r.OrderDetailId=a.Id inner join [Order] b on r.OrderId=b.Id where r.IsDeleted=0";
                if (!string.IsNullOrEmpty(begintime) && !string.IsNullOrEmpty(endtime) && begintime != "undefined" && endtime != "undefined")
                {
                    sql += " and r.AddTime>'" + begintime + "' and r.AddTime<'" + endtime + "'";
                }
                if (!string.IsNullOrEmpty(orderno) && orderno != "undefined")
                {
                    sql += " and r.OrderNo='" + orderno + "'";
                }
                if (satus >= 0)
                {
                    sql += " and r.Satus=" + satus + "";
                }
                if (returntype >= 0)
                {
                    sql += " and r.ReturnType=" + returntype + "";
                }
                var dt = db.GetDataTable(sql);
                return dt;
            }
        }

        public DataTable GetMemberList(MemberSearchModel search)
        {
            using (var Db = DbConfig.GetInstance())
            {
                Db.CurrentFilterKey = "";

                string sql = "SELECT a.Id, a.NickName, case a.Sex when 1 then '男' when 2 then '女'  ELSE '未知' END AS Sex, b.LevelName, a.Points, a.Balance, a.Addtime FROM dbo.Member AS a INNER JOIN MemberLevel AS b ON a.MemberLevel = b.Id where a.IsDeleted=0 ";

                if (!search.Keywords.IsNullOrEmpty())
                {
                    sql += " and  a.NickName like '%"+ search.Keywords + "%'";
                }
                if (search.MemberLevel > 0)
                {
                    sql += " and a.MemberLevel="+search.MemberLevel;
                }

                sql += " order by a.AddTime desc";

                var dt = Db.GetDataTable(sql);
                return dt;
            }
        }

        public DataTable ProductManage(ProductSearchModel search)
        {
            using (var Db = DbConfig.GetInstance())
            {
                Db.CurrentFilterKey = "";

                string sql = "SELECT  c.CategoryName AS CategoryName1, d.CategoryName AS CategoryName2, b.Name, b.Sku, a.SpecNames, b.Price, b.MarketPrice, CASE b.IsDisplay WHEN 0 THEN '已下架' WHEN 1 THEN '销售中' ELSE '未知' END AS IsDisplay, 'http://wx.youpinsanyue.com/#/shop/productdetail?sku=' + b.Sku AS ProductUrl "
                            +" FROM Product_Spec AS a INNER JOIN Product AS b ON a.ProductSku = b.Sku INNER JOIN Product_Category AS d ON b.CategoryId = d.Id INNER JOIN Product_Category AS c ON d.ParentId = c.Id "
                            +" WHERE(a.IsDeleted = 0) AND (b.IsDeleted = 0) ";

                if (!search.Keywords.IsNullOrEmpty() && search.Keywords != "undefined")
                {
                    sql += $" (and b.Name like '%{search.Keywords}%' or b.Sku like '%{search.Keywords}%') ";
                }

                if (search.FirstCategoryIdParm > 0)
                {
                    sql+=" and c.Id="+ search.FirstCategoryIdParm;
                    if (search.SecondCategoryIdParm > 0)
                    {
                        sql += " and d.Id="+ search.SecondCategoryIdParm;
                    }
                }

                if (search.IsDisplayParm > 0)
                {
                    if (search.IsDisplayParm == 1)
                    {
                        sql += " and b.IsDisplay=0";
                    }
                    else
                    {
                        sql += " and b.IsDisplay=1";
                    }
                }
                sql += " order by b.Id desc";
                var dt = Db.GetDataTable(sql);
                return dt;
                //Sqlable sable = Db.Sqlable().From<Product>("P");
                //sable.Join<Product_Spec>("PS", "PS.ProductSku", "P.Sku", JoinType.Left);
                //sable.Join<Product_Category>("PC", "PC.Id", "P.CategoryId", JoinType.Left);
                //sable.Join<Product_Category>("PC1", "PC1.Id", "PC.ParentId", JoinType.Left);
                //sable.Where(" P.IsDeleted=0 AND PS.IsDeleted=0 ");
                //if (!search.Keywords.IsNullOrEmpty()&&search.Keywords!= "undefined")
                //{
                //    sable.Where(" (P.Name like @Name or P.Sku like @Sku or PS.BarCode like @BarCode) ");
                //}

                //if (search.FirstCategoryIdParm > 0)
                //{
                //    sable.Where(" PC1.Id=@FirstCategoryId ");
                //    if (search.SecondCategoryIdParm > 0)
                //    {
                //        sable.Where(" PC.Id=@SecondCategoryId ");
                //    }
                //}

                //if (search.IsDisplayParm > 0)
                //{
                //    sable.Where(" P.IsDisplay=@IsDisplay ");
                //}

                //if (!search.OrderByFields.IsNullOrEmpty())
                //{
                //    search.OrderByFields = search.OrderByFields.Contains("CanSaleStock") ? search.OrderByFields.Replace("CanSaleStock", "SUM(PS.Stock-PS.WaitSendStock)") : search.OrderByFields;
                //}
                //else
                //{
                //    search.OrderByFields = "P.Id DESC";
                //}

                //var pars = new { Name = "%" + search.Keywords + "%", Sku = "%" + search.Keywords + "%", BarCode = "%" + search.Keywords + "%", FirstCategoryId = search.FirstCategoryIdParm, SecondCategoryId = search.SecondCategoryIdParm, IsDisplay = (search.IsDisplayParm == 1 ? 1 : 0) };
                //sable.GroupBy = "GROUP BY P.Id";

                ////total = sable.Count(pars);
                //sable.GroupBy = "GROUP BY P.Id,P.Sku,P.AddTime,P.FavoriteCount,P.ImgUrl,P.IsDisplay,P.IsRecommend,P.Name,P.SaleCount,P.Sort,PC1.CategoryName,PC.CategoryName";
                ////var list = sable.SelectToPageList<ProductResponse>("P.Id,P.Sku,SUM(PS.Stock) AS 'TotalStock',MIN(CASE WHEN PS.Stock>0 THEN PS.Price END) AS 'Price',P.AddTime,P.FavoriteCount,P.ImgUrl,P.IsDisplay,P.IsRecommend,P.Name,P.SaleCount,P.Sort,PC1.CategoryName+'-'+PC.CategoryName AS 'CategoryNameList',SUM(PS.Stock-PS.WaitSendStock) AS 'CanSaleStock'", search.OrderByFields, search.Skip / search.Take + 1, search.Take, pars);
                //var list1 = sable.SelectToDataTable("P.Sku,P.AddTime,P.FavoriteCount,case P.IsDisplay when 0 then '已下架' when 1 then '销售中' else '未知'  end as IsDisplay,case P.IsRecommend when 0 then '否' when 1 then '是' else '未知'  end as IsRecommend,P.Name,P.SaleCount,P.Sort,PC1.CategoryName+'-'+PC.CategoryName AS 'CategoryNameList',SUM(PS.Stock-PS.WaitSendStock) AS 'CanSaleStock'",  pars);

                //return list1;
            }
        }
    }
}
